package fhb.mobile.manager.localDB;


import fhb.mobile.todoapp.datamodell.ToDoItem;
import fhb.mobile.todoapp.datamodell.ToDoItemList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper{
	
	protected static final String DBName = "androidtodo";
	
	protected static final String TABLE_LOGIN = "login";
	protected static final String TABLE_TODO = "todo";
	
	protected static final String LOGIN_ID = "username";
	protected static final String LOGIN_PW = "password";
	
	protected static final String TODO_CREATE = "createDate";
	protected static final String TODO_TITLE = "title";
	protected static final String TODO_CONTENT = "content";
	protected static final String TODO_FAVOURITE = "favourite";
	protected static final String TODO_DEADLINE = "deadline";
	protected static final String TODO_COMPLETED = "completed";
	protected static final String TODO_UPDATED = "updated";
	protected static final String TODO_CONTACTS = "contacts";
	

	public DatabaseHandler(Context context) {
		super(context, DBName, null, 2);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_LOGIN = "CREATE TABLE " + TABLE_LOGIN + " (" + LOGIN_ID + " varchar(255) not null, " +  LOGIN_PW + " text not null, PRIMARY KEY (username) )";
		String CREATE_TODO = "CREATE TABLE " + TABLE_TODO + " (" + TODO_CREATE + " BIGINT not null, " + TODO_TITLE + " text not null, " + TODO_CONTENT + " text not null, " + TODO_FAVOURITE + " tinyint(1) not null, " + TODO_DEADLINE + " BIGINT not null, " + TODO_COMPLETED + " tinyint(1) not null, " + TODO_UPDATED + " BIGINT not null, " + TODO_CONTACTS + " text, PRIMARY KEY (createDate) )";
		db.execSQL(CREATE_TODO);
		db.execSQL(CREATE_LOGIN);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOGIN);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO);
		onCreate(db);
	}
	
	public void addToDo(ToDoItem todo){
        SQLiteDatabase db = this.getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(TODO_CREATE, todo.getCreateDate());
        values.put(TODO_TITLE, todo.getTitle());
        values.put(TODO_CONTENT, todo.getContent());
        values.put(TODO_FAVOURITE, todo.isFeatured());
        values.put(TODO_DEADLINE, todo.getDeadline());
        values.put(TODO_COMPLETED, todo.isCompleted());
        values.put(TODO_UPDATED, todo.getLastChange());
        values.put(TODO_CONTACTS, todo.getContacts());
 
        db.insert(TABLE_TODO, null, values);
        db.close(); // Closing database connection	
	}
	
	public void updateToDo(ToDoItem todo){
        SQLiteDatabase db = this.getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(TODO_CREATE, todo.getCreateDate());
        values.put(TODO_TITLE, todo.getTitle());
        values.put(TODO_CONTENT, todo.getContent());
        values.put(TODO_FAVOURITE, todo.isFeatured());
        values.put(TODO_DEADLINE, todo.getDeadline());
        values.put(TODO_COMPLETED, todo.isCompleted());
        values.put(TODO_UPDATED, todo.getLastChange());
        values.put(TODO_CONTACTS, todo.getContacts());
 
        // updating row
        db.update(TABLE_TODO, values, TODO_CREATE + " = ?", new String[] { String.valueOf(todo.getCreateDate()) });
	}
	
	public void deleteToDo(ToDoItem todo){
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_TODO, TODO_CREATE + " = ?", new String[] { String.valueOf(todo.getCreateDate()) });
        db.close();
	}
	
	public ToDoItem getToDo(String createDate){
		return null;
	}
	
	public ToDoItemList getAllToDo(boolean sort){
        ToDoItemList todos = new ToDoItemList();
        // Select All Query
        String order;
        if (sort){
        	order = ", " + TODO_DEADLINE + ", " + TODO_FAVOURITE;
        } else {
        	order = ", " + TODO_FAVOURITE + " DESC, " + TODO_DEADLINE;
        }
        String selectQuery = "SELECT  * FROM " + TABLE_TODO + " ORDER BY " + TODO_COMPLETED + order;
 
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
 
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                ToDoItem todo = new ToDoItem(cursor.getLong(0),cursor.getString(1),cursor.getString(2),cursor.getLong(4),false,false,cursor.getLong(6),null);
            	if(!cursor.getString(3).equals("0")){
            		todo.setFeatured(true);
            	}
            	if(!cursor.getString(5).equals("0")){
            		todo.setCompleted(true);
            	}
            	todo.setContacts(cursor.getString(7));
                // Adding contact to list
                todos.add(todo);
            } while (cursor.moveToNext());
        }
 
        return todos;
	}
	
	public boolean login(String username, String password){
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_LOGIN, new String[] { LOGIN_ID, LOGIN_PW}, LOGIN_ID + "=?", new String[] { username }, null, null, null, null);
		
		if ((cursor != null)&&(cursor.getCount()>0)){
			cursor.moveToFirst();
			if (password.equals(cursor.getString(1))){
				return true;
			}
		}
		return false;
	}
	
	public void addAccount(String username, String password){
        SQLiteDatabase db = this.getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(LOGIN_ID, username);
        values.put(LOGIN_PW, password);
        
 
        db.insert(TABLE_LOGIN, null, values);
        db.close(); // Closing database connection	
	}

}
